Versatile SQL database for browser or node. Handles relational data and nested JSON (noSQL). Export to and import from Excel, localStorage or IndexedDB
AlaSQL is a JavaScript library that provides a SQL-like syntax for querying and manipulating data in the browser and Node.js. It allows you to perform SQL operations on JavaScript arrays, JSON objects, and other data formats without needing a database server.
In-memory SQL Database
AlaSQL can be used to perform SQL queries on in-memory data structures like arrays and JSON objects. This example demonstrates selecting records from an array of objects using SQL syntax.
const alasql = require('alasql');
const data = [{id: 1, name: 'Alice'}, {id: 2, name: 'Bob'}];
const result = alasql('SELECT * FROM ? WHERE id = 1', [data]);
console.log(result);CSV and Excel File Processing
AlaSQL can read and write CSV and Excel files, allowing you to perform SQL operations on data stored in these formats. This example shows how to read data from a CSV file.
alasql('SELECT * FROM CSV("data.csv", {headers:true})', [], function(res) {
console.log(res);
});LocalStorage and IndexedDB Support
AlaSQL can store and retrieve data from browser storage solutions like LocalStorage and IndexedDB. This example demonstrates storing a table in LocalStorage.
alasql('CREATE TABLE test (id INT, name STRING)');
alasql('INSERT INTO test VALUES (1, "Alice"), (2, "Bob")');
alasql('SELECT * INTO localStorage FROM test');Advanced SQL Features
AlaSQL supports advanced SQL features such as GROUP BY, JOIN, and more. This example shows how to use the GROUP BY clause to aggregate data.
alasql('SELECT name, COUNT(*) AS count FROM ? GROUP BY name', [data]);sql.js is a JavaScript library that allows you to run SQLite in the browser. It provides a full SQLite database engine compiled with Emscripten, enabling you to perform SQL operations on a persistent database. Unlike AlaSQL, which is more focused on in-memory operations, sql.js provides a more traditional database experience with file-based persistence.
node-sqlite3 is a Node.js library that provides bindings to SQLite, allowing you to perform SQL operations on SQLite databases. It is more suitable for server-side applications where you need a lightweight, file-based database. Compared to AlaSQL, node-sqlite3 offers more robust database features and is better suited for applications requiring persistent storage.
AlaSQL is an open source project, and we appreciate any and all contributions we can get. Please help out.
Got a question? Ask on Stack Overflow and tag with "alasql".
AlaSQL - ( à la SQL ) [ælæ ɛskju:ɛl] - is an open source SQL database for Javascript with a strong focus on query speed and data source flexibility for both relational data and schemaless data. It works in your browser, Node.js, and Cordova.
This library is designed for:
We focus on speed by taking advantage of the dynamic nature of JavaScript when building up queries. Real-world solutions demand flexibility regarding where data comes from and where it is to be stored. We focus on flexibility by making sure you can import/export and query directly on data stored in Excel (both .xls and .xlsx), CSV, JSON, TAB, IndexedDB, LocalStorage, and SQLite files.
The library adds the comfort of a full database engine to your JavaScript app. No, really - it's working towards a full database engine complying with most of the SQL-99 spiced up with an additional syntax for handling NoSQL (schema-less) data and graph networks.
// A) Traditional SQL
alasql("CREATE TABLE cities (city string, population number)");
alasql("INSERT INTO cities VALUES ('Rome',2863223),('Paris',2249975),('Berlin',3517424),('Madrid',3041579)");
var res = alasql("SELECT * FROM cities WHERE population < 3500000 ORDER BY population DESC");
console.log(res);
/*
[
{
"city": "Madrid",
"population": 3041579
},
{
"city": "Rome",
"population": 2863223
},
{
"city": "Paris",
"population": 2249975
}
]
*/
// B) SQL on array of objects
var data = [{a:1,b:10}, {a:2,b:20}, {a:1,b:30}];
var res = alasql('SELECT a, SUM(b) AS b FROM ? GROUP BY a',[data]);
console.log(res); // [{"a":1,"b":40},{"a":2,"b":20}]
// C) Read from file must be async (Promise returned when SQL given as array)
alasql(['SELECT * FROM XLS("./data/mydata") WHERE lastname LIKE "A%" and city = "London" GROUP BY name '])
.then(function(res){
console.log(res); // output depends on mydata.xls
}).catch(function(err){
console.log('Does the file exist? There was an error:', err);
});
// D) Cheat and load your data directly
alasql("CREATE TABLE example1 (a INT, b INT)");
alasql.tables.example1.data = [ // Insert data directly from JavaScript object...
{a:2,b:6},
{a:3,b:4}
];
alasql("INSERT INTO example1 VALUES (1,5)"); // ...or insert data with normal SQL
var res = alasql("SELECT * FROM example1 ORDER BY b DESC");
console.log(res); // [{a:2,b:6},{a:1,b:5},{a:3,b:4}]
jsFiddle with example A) and example B)
If you are familiar with SQL it should come as no surprise that proper usage of indexes on your tables is essential to get good performance.
npm install --save alasql # node
bower install --save alasql # bower
import alasql from 'alasql'; # meteor
npm install -g alasql # command line
For the browser: include alasql.min.js
<script src="http://cdn.jsdelivr.net/alasql/0.3/alasql.min.js"></script>
The wiki has a great section on how to get started
When you feel you've gotten a grip, you can check out the wiki section about data manipulation or get inspired by the list of Q&As
Documentation: Github wiki
Library CDN: jsDelivr.com
Feedback: Open an issue
Try online: Playground
Website: alasql.org
All contributions are extremely welcome and greatly appreciated(!) - The project has never received any funding and is based on unpaid voluntary work: We really (really) love pull requests
AlaSQL project is very young and still in an active development phase, therefore it may have bugs.
Please, submit any bugs and suggestions as an issue.
AlaSQL uses Semantic Versioning so please note that the major version is zero (0.y.z) and the API can not be considered 100% stable. Consider this before using the library in production and please check out the limitations of the library
AlaSQL is very focused on speed, and we make sure to use all the tricks we can find to make JavaScript spit out your results as quickly as possible. For example:
WHERE expressions are pre-filtered for joinsThe results are good. Check out AlaSQL vs. other JavaScript SQL databases:
3x speed compared to SQL.js selecting with SUM, JOIN, and GROUP BY.
1x speed compared to WebSQL selecting with SUM, JOIN, and GROUP BY (in-memory operations for WebSQL - see this discussion)
2x speed compared to Linq for GROUP BY on 1,048,576 rows
Please remember to set indexes on your tables to speed up your queries. Have a look here if you are unfamiliar with this concept.
See more speed related info on the wiki
Use "good old" SQL on your data with multiple levels of: JOIN, VIEW, GROUP BY, UNION, PRIMARY KEY, ANY, ALL, IN, ROLLUP(), CUBE(), GROUPING SETS(), CROSS APPLY, OUTER APPLY, WITH SELECT, and subqueries. See the wiki to compare supported features with SQL standards.
You can use all benefits of SQL and JavaScript together by defining your own custom functions. Just add new functions to the alasql.fn object:
alasql.fn.myfn = function(a,b) {
return a*b+1;
}
var res = alasql('SELECT myfn(a,b) FROM one');
You can also define your own aggregator functions (like your own SUM(...)). See more in the wiki
var ins = alasql.compile('INSERT INTO one VALUES (?,?)');
ins(1,10);
ins(2,20);
See more in the wiki
Group your JavaScript array of objects by field and count number of records in each group:
var data = [{a:1,b:1,c:1},{a:1,b:2,c:1},{a:1,b:3,c:1}, {a:2,b:1,c:1}];
var res = alasql('SELECT a, COUNT(*) AS b FROM ? GROUP BY a',[data]);
console.log(res);
See more ideas for creative data manipulation in the wiki
AlaSQL extends "good old" SQL to make it closer to JavaScript. The "sugar" includes:
{a:'1',b:@['1','2','3']}obj->property->subpropertyobj->(a*1)obj->valueOf()SELECT VALUE, ROW, COLUMN, MATRIXvar SQL = function(){/*select 'MY MULTILINE SQL'*/} and pass instead of SQL string (will not work if you compress your code)You can import from and export to CSV, TAB, TXT, and JSON files. File extensions can be omitted. Calls to files will always be [[async]] so the approach is to chain the queries if you have more than one:
var tabFile = 'mydata.tab'
alasql.promise([
"select * from txt('MyFile.log') where [0] like 'M%'",
["select * from tab(?) order by [1]", [tabFile]], // note how to pass parameter when promises are chained
"select [3] as city,[4] as population from csv('./data/cities')",
"select * from json('../config/myJsonfile')"
]).then(function(results){
console.log(results)
}).catch(console.error)
AlaSQL can read (but not write) SQLite data files if you include the SQL.js library:
<script src="alasql.js"></script>
<script src="sql.js"></script>
<script>
alasql(['ATTACH SQLITE DATABASE Chinook("Chinook_Sqlite.sqlite")',
'USE Chinook',
'SELECT * FROM Genre'
]).then(function(res){
console.log("Genres:",res.pop());
});
</script>
sql.js calls will always be async.
After globally installing AlaSQL npm install alasql -g you can access AlaSQL via the commandline
> alasql "SET @data = @[{a:'1',b:?},{a:'2',b:?}]; SELECT a, b FROM @data;" 10 20
[ 1, [ { a: 1, b: 10 }, { a: 2, b: 20 } ] ]
> alasql "VALUE OF SELECT COUNT(*) as abc FROM TXT('README.md') WHERE LENGTH([0]) > ?" 140
// Number of lines with more than 140 characters in README.md
See more in the wiki
AlaSQL plays nice with d3.js and gives you a convenient way to integrate a specific subset of your data with the visual powers of D3. See more about D3.js and AlaSQL in the wiki
AlaSQL can export data to both Excel 2003 (.xls) and Excel 2007 (.xlsx) formats with coloring of cells and other Excel formatting functions.
Meteor is amazing. You can query directly on your Meteor collections with SQL - simple and easy. See more about Meteor and AlaSQL in the wiki
Angular is great. In addition to normal data manipulation, AlaSQL works like a charm for exporting your present scope to Excel. See more about Angular and AlaSQL in the wiki
Pinpointing data on a map should be easy. AlaSQL is great to prepare source data for Google Maps from, for example, Excel or CSV, making it one unit of work for fetching and identifying what's relevant. See more about Google Maps and AlaSQL in the wiki
AlaSQL can query data directly from a Google spreadsheet. A good "partnership" for easy editing and powerfull data manipulation. See more about Google Spreadsheets and AlaSQL in the wiki
Take charge and add your idea or vote for your favorite feature to be implemented:
Please be aware that AlaSQL has bugs. Beside having some bugs, there are a number of limitations:
AlaSQL has a (long) list of keywords that must be escaped if used for column names. When selecting a field named key please write SELECT `key` FROM ... instead. This is also the case for words like `value`, `read`, `count`, `by`, `top`, `path`, `deleted`, `work` and `offset`. Please consult the full list of keywords.
It is OK to SELECT 1000000 records or to JOIN two tables with 10000 records in each (You can use streaming functions to work with longer datasources - see test/test143.js) but be aware that the workload is multiplied so SELECTing from more than 8 tables with just 100 rows in each will show bad performance. This is one of our top priorities to make better.
Limited functionality for transactions (supports only for localStorage) - Sorry, transactions are limited, because AlaSQL switched to more complex approach for handling PRIMARY KEYs / FOREIGN KEYs. Transactions will be fully turned on again in future version.
A (FULL) OUTER JOIN and RIGHT JOIN of more than 2 tables will not produce expected results. INNER JOIN and LEFT JOIN are OK.
Please use aliases when you want fields with same name from different tables (SELECT a.id as a_id, b.id as b_id FROM ?).
At the moment AlaSQL does not work with JSZip 3.0.0 - please use version 2.x.
JOINing a sub-SELECT does not work. Please store your sub-SELECT in a temporary table (or fetch the sub-SELECT and pass it as an argument).
AlaSQL uses the FileSaver.js library for saving files locally from the browser. Please be aware that it does not save files in Safari 8.0.
There are probably many others. Please help us fix them by submitting an issue. Thank you!
ETL example:
alasql(['CREATE TABLE IF NOT EXISTS geo.country',
'SELECT * INTO geo.country FROM CSV("country.csv",{headers:true})',
'SELECT * INTO XLSX("asia") FROM geo.country WHERE continent_name = "Asia"'
).then(function(res){
... // results from the file asia.xlsx
});
AlaSQL can serve as a Web Worker. Please be aware that all interaction with AlaSQL when running must be async.
In the browser you can include alasql-worker.min.js instead of alasql.min.js and AlaSQL will figure out the rest:
<script src="alasql-worker.min.js"></script>
<script>
var arr = [{a:1},{a:2},{a:1}];
alasql([['SELECT * FROM ?',[arr]]]).then(function(data){
console.log(data);
});
</script>
Try the jsFiddle example.
Another option is to include alasql.min.js as usual but call alasql.worker() as the first thing yourself:
<script src="alasql.min.js"></script>
<script>
alasql.worker();
var res = alasql(['select value 10']).then(function(res){
console.log(res);
}).catch(console.error);
</script>
Try this jsFiddle example.
If using AlaSQL as Web Worker, you can import it traditionally as a script:
importScripts('alasql.min.js');
When targeting the browser, several code bundlers like Webpack and Browserify will pick up modules you might not want.
Here's a list of modules that AlaSQL requires:
There are several ways to handle AlaSQL with Webpack:
Ideal when you want to control which modules you want to import.
var IgnorePlugin = require("webpack").IgnorePlugin;
module.exports = {
...
//Will ignore the modules fs, path, xlsx
plugins:[new IgnorePlugin(/(^fs$|cptable|jszip|xlsx|^es6-promise$|^net$|^tls$|^forever-agent$|^tough-cookie$|cpexcel|^path$)/)]
};
As of AlaSQL 0.3.5, you can simply tell Webpack not to parse AlaSQL, which avoids all the dynamic require warnings and avoids using eval/clashing with CSP with script-loader.
Read the Webpack docs about noParse
...
//Don't parse alasql
{module:noParse:[/alasql/]}
If both of the solutions above fail to meet your requirements, you can load AlaSQL with script-loader.
//Load alasql in the global scope with script-loader
import "script!alasql"
This can cause issues if you have a CSP that doesn't allow eval.
Read up on excluding, ignoring, and shimming
Example (using excluding)
var browserify = require("browserify");
var b = browserify("./main.js").bundle();
//Will ignore the modules fs, path, xlsx
["fs","path","xlsx", ... ].map(ignore => b.ignore(ignore));
Please remember to send the original event, and not the jQuery event, for elements. (Use event.originalEvent instead of myEvent)
You can use JSON objects in your databases (do not forget use == and !== operators for deep comparision of objects):
alasql> SELECT VALUE {a:'1',b:'2'}
{a:1,b:2}
alasql> SELECT VALUE {a:'1',b:'2'} == {a:'1',b:'2'}
true
alasql> SELECT VALUE {a:'1',b:'2'}->b
2
alasql> SELECT VALUE {a:'1',b:(2*2)}->b
4
Try AlaSQL JSON objects in Console [sample](http://alasql.org/console?drop table if exists one;create table one;insert into one values {a:@[1,2,3],c:{e:23}}, {a:@[{b:@[1,2,3]}]};select * from one)
Useful stuff, but there might be dragons
AlaSQL is a multi-paradigm database with support for graphs that can be searched or manipulated.
// Who loves lovers of Alice?
var res = alasql('SEARCH / ANY(>> >> #Alice) name');
console.log(res) // ['Olga','Helen']
See more at the wiki
You can use browser localStorage and DOM-storage as a data storage. Here is a sample:
alasql('CREATE localStorage DATABASE IF NOT EXISTS Atlas');
alasql('ATTACH localStorage DATABASE Atlas AS MyAtlas');
alasql('CREATE TABLE IF NOT EXISTS MyAtlas.City (city string, population number)');
alasql('SELECT * INTO MyAtlas.City FROM ?',[[{city:'Vienna', population:1731000},
{city:'Budapest', population:1728000}]]);
var res = alasql('SELECT * FROM MyAtlas.City');
console.log(res);
Try this sample in jsFiddle. Run this sample two or three times, and AlaSQL store more and more data in localStorage. Here, "Atlas" is the name of localStorage database, where "MyAtlas" is a memory AlaSQL database.
You can use localStorage in two modes: SET AUTOCOMMIT ON to immediate save data
to localStorage after each statement or SET AUTOCOMMIT OFF. In this case, you need
to use COMMIT statement to save all data from in-memory mirror to localStorage.
AlaSQL supports plugins. To install a plugin you need to use the REQUIRE statement. See more at the wiki
Yes, you can even use AlaSQL as a very simple server for tests.
To run enter the command:
alaserver [port]
then type in browser something like "http://127.0.0.1:1337/?SELECT VALUE 2*2"
Warning: Alaserver is not multi-threaded, not concurrent, and not secured.
AlaSQL currently has over 1200 regression tests, but they only cover
of the codebase.
AlaSQL uses mocha for regression tests. Install mocha and run
> npm test
or run test/index.html for in-browser tests (Please serve via localhost with, for example, http-server).
You can use AlaSQL's ASSERT operator to test the results of previous operation:
CREATE TABLE one (a INT);
ASSERT 1;
INSERT INTO one VALUES (1),(2),(3);
ASSERT 3;
SELECT * FROM one ORDER BY a DESC;
ASSERT [{a:3},{a:2},{a:1}];
AlaSQL uses SQLLOGICTEST to test its compatibility with SQL-99. The tests include about 2 million queries and statements.
The testruns can be found in the testlog.
If you want to try the most recent development version of the library please download this file or visit the testbench to play around in the browser console.
MIT - see MIT licence information
AlaSQL is an OPEN Open Source Project. This means that:
Individuals making significant and valuable contributions are given commit-access to the project to contribute as they see fit. This project is more like an open wiki than a standard guarded open source project.
We appreciate any and all contributions we can get. If you feel like contributing, have a look at CONTRIBUTING.md._
Many thanks to Zach Carter for Jison parser generator, to the author of FileSaver.js, Andrew Kent for his SQL Parser, authors of XLSX library, and other people for useful tools, which make our work much easier.
© 2014-2017, Andrey Gershun (agershun@gmail.com) & Mathias Rangel Wulff (m@rawu.dk)
FAQs
Use SQL to select and filter javascript data - including relational joins and search in nested objects (JSON). Export to and import from Excel and CSV
The npm package alasql receives a total of 137,190 weekly downloads. As such, alasql popularity was classified as popular.
We found that alasql demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 2 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Research
Security News
Malicious Ruby gems typosquat Fastlane plugins to steal Telegram bot tokens, messages, and files, exploiting demand after Vietnam’s Telegram ban.
Research
Security News
Socket uncovered four malicious npm packages that exfiltrate up to 85% of a victim’s Ethereum or BSC wallet using obfuscated JavaScript.
Security News
TC39 advances 9 JavaScript proposals, including Array.fromAsync, Error.isError, and Explicit Resource Management, which are now headed into the ECMAScript spec.